package database;

import java.sql.*;
import java.util.ArrayList;

//import com.sun.xml.internal.ws.org.objectweb.asm.Type;

public class Database {

	private Connection con = null;

	/*
	 * connect to database
	 */
	public void connectJDBC(String username, String password) throws SQLException {

		// load jdbc driver
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}

		// Establish the connection
		String url = "jdbc:oracle:thin:@oracle2.gl.umbc.edu:1521:GL";

		con = DriverManager.getConnection(url, username, password);
		System.out.println("\nConnected to Oracle.");
		System.out.println("---------------");

	}

	/*
	 * Every time the user hits a button (almost) the database must be updated
	 * Before this function can be called the "insert into" statements need to
	 * be calculated first and given as a parameter to this function.
	 */
	public void insertIntoDatabase(String sqlInsert) {
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sqlInsert);
			stmt.close();
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			ex.printStackTrace(System.err);
			System.exit(1);

		}
	}

	public void sqlcommit() {
		try {
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public int executeUpdate(String sql) {
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
			return 0;
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}

			//ex.printStackTrace(System.err);
			return -1;
		}
	}

	public int executeProcedure(String sql) {
		try {
			CallableStatement stmt = con.prepareCall(sql);
			stmt.executeUpdate();
			return 0;
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {

				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block

			return -1;
		}

	}

	public int executeFunction(String sql) {

		try {
			CallableStatement stmt = con.prepareCall(sql);
			stmt.registerOutParameter(1, Types.INTEGER);
			stmt.execute();
			return stmt.getInt(1);
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				//e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}
		return 0;

	}

	public String[] executeQuery(String statement, int numCols) {

		ArrayList<String> list = new ArrayList<String>();
		try {
			Statement stmt = con.createStatement();
			ResultSet result = stmt.executeQuery(statement);

			while (result.next()) {
				String item = "";
				for (int i = 1; i <= numCols; i++) {
					item = item + result.getString(i) + ",";
					// System.out.println(name);
				}
				list.add(item.substring(0, item.length() - 1));
			}
			stmt.close();
			String[] listData = list.toArray(new String[list.size()]);
			if (listData.length > 0) {
				return listData;
			} else {
				return null;
			}

			// System.out.println("\nDone successfully");
			// System.out.println("---------------");
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			System.exit(1);
		}
		return null;

	

	}

  

}
